package com.zy.dao.impl;

import com.zy.dao.SalaryDao;
import com.zy.pojo.Department;
import com.zy.pojo.Salary;
import com.zy.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: xiaoqi
 * Date: 2021-06-11
 * Time: 下午 16:32
 * Description: 工资持久层接口实现类
 */
public class SalaryDaoImpl implements SalaryDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public Integer insertSalary(Salary salary) {
        String sql = "insert into salary (empId , money) values (? , ?)";
        return jdbcTemplate.update(sql, salary.getEmpId(), salary.getMoney());
    }

    @Override
    public List<Salary> getSalaryListByPage(Salary salary) {
        StringBuilder sb = new StringBuilder("SELECT\n" +
                "\tt1.*,\n" +
                "\tt2.`name` empName,\n" +
                "\tt3.departmentName,\n" +
                "\tt4.roleName, \n" +
                "\tt2.enterDate \n" +
                "FROM\n" +
                "\tsalary t1\n" +
                "\tLEFT JOIN employee t2 ON t1.empId = t2.id\n" +
                "\tLEFT JOIN role t4 ON t2.id = t4.empId\n" +
                "\tLEFT JOIN department t3 ON t2.departmentId = t3.id WHERE 1 = 1 ");

        ArrayList<Object> params = new ArrayList<>();
        if (salary.getRoleName() != null && !"".equals(salary.getRoleName())) {
            params.add(salary.getRoleName());
            sb.append(" and t4.roleName = ? ");
        }
        if (salary.getDepartmentName() != null && !"".equals(salary.getDepartmentName())) {
            params.add(salary.getDepartmentName());
            sb.append(" and t3.departmentName = ? ");
        }
        if (salary.getStartMoney() != null) {
            params.add(salary.getStartMoney());
            sb.append(" and t1.money > ? ");
        }
        if (salary.getEndMoney() != null) {
            params.add(salary.getEndMoney());
            sb.append(" and t1.money < ? ");
        }

        if (salary.getEmpName() != null && !"".equals(salary.getEmpName())) {
            params.add(salary.getEmpName());
            sb.append(" and t2.name like concat('%' , ? '%') ");
        }

        List<Salary> list = new ArrayList<Salary>();
        try {
            if (salary.getStart() != null && salary.getLimit() != null) {
                params.add(salary.getStart());
                params.add(salary.getLimit());
                sb.append(" ORDER BY t2.enterDate limit ? , ? ");
                System.out.println(sb.toString());
                list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<Salary>(Salary.class), params.toArray());
            } else {
                list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<Salary>(Salary.class), params.toArray());
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return list;
        }
    }

    @Override
    public Integer getSalaryCount(Salary salary) {
        StringBuilder sb = new StringBuilder("SELECT\n" +
                " count(*) " +
                "FROM\n" +
                "\tsalary t1\n" +
                "\tLEFT JOIN employee t2 ON t1.empId = t2.id\n" +
                "\tLEFT JOIN role t4 ON t2.id = t4.empId\n" +
                "\tLEFT JOIN department t3 ON t2.departmentId = t3.id WHERE 1 = 1 ");

        ArrayList<Object> params = new ArrayList<>();
        if (salary.getRoleName() != null && !"".equals(salary.getRoleName())) {
            params.add(salary.getRoleName());
            sb.append(" and t4.roleName = ? ");
        }
        if (salary.getDepartmentName() != null && !"".equals(salary.getDepartmentName())) {
            params.add(salary.getDepartmentName());
            sb.append(" and t3.departmentName = ? ");
        }
        if (salary.getStartMoney() != null) {
            params.add(salary.getStartMoney());
            sb.append(" and t1.money > ? ");
        }
        if (salary.getEndMoney() != null) {
            params.add(salary.getEndMoney());
            sb.append(" and t1.money < ? ");
        }

        if (salary.getEmpName() != null && !"".equals(salary.getEmpName())) {
            params.add(salary.getEmpName());
            sb.append(" and t2.name like concat('%' , ? '%') ");
        }
        return jdbcTemplate.queryForObject(sb.toString(), Integer.class, params.toArray());
    }

    @Override
    public Integer deleteSalaryByEmpId(Integer id) {
        String sql = "delete from salary where empId = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Integer updateSalary(Salary salary) {
        String sql = "update salary set money = ? where empId = ?";
        System.out.println(sql);
        return jdbcTemplate.update(sql, salary.getMoney(), salary.getEmpId());
    }

    @Override
    public Salary getSalaryByEmpId(Integer empId) {
        String sql = "select * from salary where empId = ?";
        Salary salary = null;
        try {
            salary = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Salary>(Salary.class), empId);
        } catch (DataAccessException e) {
            return null;
        }
        return salary;
    }
}